package com.gestionabsences.form;

import com.gestionabsences.beans.MapStudents;
import com.gestionabsences.beans.ResultRequest;
import com.gestionabsences.config.DAOFactory;
import com.gestionabsences.database.ToolsRequest;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Map;
import javax.servlet.http.HttpServletRequest;

/**
 *
 * @author matt
 */
public class FormSecretaryJustification {
    public static final String CHAMP_STUDENT = "etudiant";
    public static final String CHAMP_ERREUR = "erreur";
    public static final String CHAMP_ETUDIANT_SPECIFIE = "etudiantSpecifie";
    public static final String CHAMP_ID_SEANCE_SPECIFIE = "id_seanceSpecifie";
    public static final String CHAMP_ID_MATIERE_SPECIFIE = "id_matiereSpecifie";
    public static final String CHAMP_ISA_SPECIFIE = "isASpecifie";
    public static final String CHAMP_ID_SEANCE = "id_seance";
    public static final String CHAMP_ID_MATIERE = "id_matiere";
    public static final String CHAMP_ISA = "isA";
    public static final String CHAMP_NUM_TUPLE = "numTuple";
    public static final String CHAMP_SEANCE = "seance";
    public static final String CHAMP_JUSTIFICATION = "justification";
    public static final String CHAMP_CREDIBILITE = "credibilite";
    
    private Map<String, String> erreurs;
    private MapStudents listeEtudiantsAbsents;
    private DAOFactory dao;
    private String immatriculation;
    
    private String formerValEtudiantAbsent;
    private String formerValIDSeance;
    private String formerValIDMatiere;
    private String formerValTypeSeance;
    
    public FormSecretaryJustification(DAOFactory dao, MapStudents listeEtudiantsAbsents) {
	this.dao = dao;
	this.erreurs = new HashMap<String, String>();
	this.listeEtudiantsAbsents = listeEtudiantsAbsents;
    }
    
    public ResultRequest recuperationListeAbsences( HttpServletRequest request ) {
	
	String ineEtudiant = getValeurChamp( request, CHAMP_STUDENT );
	
	try {
            validationEtudiantAbsent( ineEtudiant );
        } catch ( Exception e ) {
            setErreur( CHAMP_STUDENT, e.getMessage() );
        }
	
	ResultRequest res = null;
	
	if( erreurs.isEmpty() ) {
	    this.formerValEtudiantAbsent = ineEtudiant;
	    
	    res = createListAbsentStudentsFromArgs(ineEtudiant);
	    if(res != null) {
		if( !res.isResultatPresent() ) {
		    res = null;
		}
	    }
	}
	
	return res;
    }
    
    public ResultRequest recuperationAbsence( HttpServletRequest request ) {
	
	String ineEtudiantSelected = getValeurChamp( request, CHAMP_ETUDIANT_SPECIFIE );
	String id_seance = null;
	String id_matiere = null;
	String typeSeance = null;
	
	String id_tuple = getValeurChamp(request, CHAMP_NUM_TUPLE);
	if (id_tuple != null) {
	    id_seance = getValeurChamp(request, CHAMP_ID_SEANCE + id_tuple);
	    id_matiere = getValeurChamp(request, CHAMP_ID_MATIERE + id_tuple);
	    typeSeance = getValeurChamp(request, CHAMP_ISA + id_tuple);
	}
	
	try {
	    validationEtudiantAbsent(ineEtudiantSelected);

	    try {
		validationSeance(id_seance, id_matiere, typeSeance);
	    } catch (Exception e) {
		setErreur(CHAMP_SEANCE, e.getMessage());
	    }

	} catch ( Exception e ) {
            setErreur( CHAMP_ETUDIANT_SPECIFIE, e.getMessage() );
        }
	
	ResultRequest res = null;
	
	if( erreurs.isEmpty() ) {
	    this.formerValEtudiantAbsent = ineEtudiantSelected;
	    this.formerValIDMatiere = id_matiere;
	    this.formerValIDSeance = id_seance;
	    this.formerValTypeSeance = typeSeance;
	    
	    res = getAbsence(ineEtudiantSelected, id_seance, id_matiere, typeSeance);
	    if(res != null) {
		if( !res.isResultatPresent() ) {
		    res = null;
		}
	    }
	}
	
	return res;
    }
    
    public boolean recupEtModifJustification( HttpServletRequest request ) {
	
	String ineEtudiantSelected = getValeurChamp( request, CHAMP_ETUDIANT_SPECIFIE );
	String id_seance = getValeurChamp( request, CHAMP_ID_SEANCE_SPECIFIE );
	String id_matiere = getValeurChamp( request, CHAMP_ID_MATIERE_SPECIFIE );
	String typeSeance = getValeurChamp( request, CHAMP_ISA_SPECIFIE );
	String justification = getValeurChamp( request, CHAMP_JUSTIFICATION );
	String credibilite = getValeurChamp( request, CHAMP_CREDIBILITE );
	
	try {
	    validationEtudiantAbsent(ineEtudiantSelected);

	    try {
		validationSeance(id_seance, id_matiere, typeSeance);
		
		try {
		    validationCredibilite(credibilite);
		} catch (Exception e) {
		    setErreur(CHAMP_CREDIBILITE, e.getMessage());
		}
	    } catch (Exception e) {
		setErreur(CHAMP_SEANCE, e.getMessage());
	    }
	} catch ( Exception e ) {
            setErreur( CHAMP_ETUDIANT_SPECIFIE, e.getMessage() );
        }
	
	boolean res = false;
	
	if( erreurs.isEmpty() ) {	    
	    res = updateAbsence(ineEtudiantSelected, id_seance, id_matiere, typeSeance, justification, credibilite);
	}
	
	return res;
    }
    
    private boolean updateAbsence(String ineEtudiant, String id_seance, String id_matiere, String typeSeance, String justification, String credibilite) {
	String sql_req = "SELECT changeAbsenceJustification(?, " + id_seance + ", " + id_matiere + ", CAST(? AS Type_Seance), ?, " + credibilite + ");";
	
	Connection connexion = null;

	try {
	    connexion = dao.getConnection();

	    PreparedStatement preparedStatement = null;
	    ResultSet resultSet = null;

	    try {	    
		    preparedStatement = ToolsRequest.prepareRequest(connexion, sql_req, false, ineEtudiant, typeSeance, justification);
		    System.out.println("query :  " + preparedStatement.toString());
		    preparedStatement.executeQuery();
	    } catch (Exception e) {
		setErreur(CHAMP_ERREUR, preparedStatement.toString() + " => " + e.getMessage());
	    } finally {
		ToolsRequest.close(preparedStatement, resultSet);
	    }
	} catch (SQLException e) {
	    setErreur(CHAMP_ERREUR, e.getMessage());
	} finally {
	    ToolsRequest.close(connexion);
	}

	if( this.erreurs.isEmpty() ) {
	    return true;
	} else {
	    return false;
	}
    }
    
    private ResultRequest getAbsence(String ineEtudiant, String id_seance, String id_matiere, String typeSeance) {
	ArrayList<String> tabColonnes = new ArrayList<String>();
	tabColonnes.add("justification");
	tabColonnes.add("credibilite");
	
	String sql_req = "SELECT A.justification, CAST(A.credibilite AS TEXT) credibilite FROM Absence A WHERE A.ine = ? AND A.id_seance = " + id_seance + " AND A.matiere = " + id_matiere + " AND A.isA = CAST(? AS Type_Seance);";
	
	Connection connexion = null;
	ResultRequest res = null;
	
	try {
	    connexion = dao.getConnection();
	    
	    PreparedStatement preparedStatement = null;
	    ResultSet resultSet = null;
	
	    try {
		preparedStatement = ToolsRequest.prepareRequest( connexion, sql_req, false, ineEtudiant, typeSeance);
		//setErreur( CHAMP_ERREUR, preparedStatement.toString() );
		System.out.println("query :  " + preparedStatement.toString());
		
		resultSet = preparedStatement.executeQuery();
		
		res = new ResultRequest(resultSet, tabColonnes.toArray());
				
	    } catch(Exception e) {
		setErreur( CHAMP_ERREUR, preparedStatement.toString() + " => " + e.getMessage() );
	    }
	    finally {
		ToolsRequest.close(preparedStatement, resultSet);
	    }
	} catch (SQLException e) {
	    setErreur( CHAMP_ERREUR, e.getMessage() );
	}
	finally {
	    ToolsRequest.close(connexion);
	}
	
	return res;
    }
    
    private ResultRequest createListAbsentStudentsFromArgs(String ineEtudiant) {
	
	ArrayList<String> tabColonnes = new ArrayList<String>();
	tabColonnes.add("id_seance");
	tabColonnes.add("id_matiere");
	tabColonnes.add("isA");
	tabColonnes.add("matiere");
	tabColonnes.add("nom_enseignant");
	tabColonnes.add("prenom_enseignant");
	tabColonnes.add("justification");
	tabColonnes.add("credibilite");
	tabColonnes.add("heure");
	
	String sql_req = "SELECT * FROM getAbsencesFor(?)";
	
	Connection connexion = null;
	ResultRequest res = null;
	
	try {
	    connexion = dao.getConnection();
	    
	    PreparedStatement preparedStatement = null;
	    ResultSet resultSet = null;
	
	    try {
		preparedStatement = ToolsRequest.prepareRequest( connexion, sql_req, false, ineEtudiant);
		//setErreur( CHAMP_ERREUR, preparedStatement.toString() );
		System.out.println("query :  " + preparedStatement.toString());
		
		resultSet = preparedStatement.executeQuery();
		
		res = new ResultRequest(resultSet, tabColonnes.toArray());
				
	    } catch(Exception e) {
		setErreur( CHAMP_ERREUR, preparedStatement.toString() + " => " + e.getMessage() );
	    }
	    finally {
		ToolsRequest.close(preparedStatement, resultSet);
	    }
	} catch (SQLException e) {
	    setErreur( CHAMP_ERREUR, e.getMessage() );
	}
	finally {
	    ToolsRequest.close(connexion);
	}
	
	return res;
    }
    
    private void validationEtudiantAbsent( String ineEtudiant ) throws Exception {
	if (ineEtudiant != null) {
	    if (!this.listeEtudiantsAbsents.containsKey(ineEtudiant)) {
		throw new Exception("L'étudiant spécifié n'existe pas ou n'est pas un étudiant absent.");
	    }
	} else {
	    throw new Exception("Un étudiant doit être spécifiée pour effectuer la modification de justification.");
	}
    }
    
    private void validationCredibilite(String credibilite) throws Exception {
	int temp;
	try {
	    temp = Integer.parseInt(credibilite);
	    if (temp < 0 || temp > 3) {
		throw new Exception("La crédibilité spécifiée est hors intervalle.");
	    }
	} catch (NumberFormatException e) {
	    throw new Exception("La crédibilité doit être spécifiée.");
	}
    }
    
    private void validationSeance( String id_seance, String id_matiere, String isA) throws Exception {
	if(isA != null && id_seance != null && id_matiere != null) {
	    
	    if(isA.compareTo("TPS") != 0 && isA.compareTo("TDS") != 0 && isA.compareTo("COURS") != 0) {
		throw new Exception("Le type de séance spécifiée n'existe pas.");
	    }
	    
	    int temp;
	    try {
		temp = Integer.parseInt(id_seance);
		if (temp < 0) {
		    throw new Exception("Le numéro de séance spécifié n'existe pas.");
		}
	    } catch (NumberFormatException e) {
		throw new Exception("Le numéro de séance spécifié n'existe pas.");
	    }
	    
	    try {
		temp = Integer.parseInt(id_matiere);
		if (temp < 0) {
		    throw new Exception("Le numéro de séance spécifié n'existe pas.");
		}
	    } catch (NumberFormatException e) {
		throw new Exception("Le numéro de séance spécifié n'existe pas.");
	    }
	    
	    Connection connexion;
	    boolean exist = false;
	    String sql_req = "SELECT S.id_seance FROM Seance S WHERE S.id_seance = " + id_seance + " AND S.matiere = " + id_matiere + " AND S.isA = '" + isA + "';";
	    
	    try {
		connexion = dao.getConnection();

		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;

		try {
		    preparedStatement = ToolsRequest.prepareRequest(connexion, sql_req, false);
		    //setErreur( CHAMP_ERREUR, preparedStatement.toString() );
		    System.out.println("query :  " + preparedStatement.toString());

		    resultSet = preparedStatement.executeQuery();

		    if( resultSet.next() ) {
			exist = true;
		    }

		} catch (Exception e) {
		    setErreur(CHAMP_ERREUR, preparedStatement.toString() + " => " + e.getMessage());
		} finally {
		    ToolsRequest.close(preparedStatement, resultSet);
		}
	    } catch (SQLException e) {
		setErreur(CHAMP_ERREUR, e.getMessage());
	    }
	    
	    if(!exist) {
		throw new Exception("La séance spécifiée n'existe pas.");
	    }
	    
	} else {
	    throw new Exception("Un étudiant doit être spécifiée pour effectuer la modification de justification.");
	}
    }
    
    public String getFormerValIDSeance () {
	return this.formerValIDSeance;
    }
    
    public String getFormerValIDMatiere () {
	return this.formerValIDMatiere;
    }
    
    public String getFormerValTypeSeance () {
	return this.formerValTypeSeance;
    }
    
    public String getFormerValEtudiantAbsent () {
	return this.formerValEtudiantAbsent;
    }
    
    public Map<String, String> getErreurs() {
        return erreurs;
    }
    
    private void setErreur( String champ, String message ) {
	String previous = erreurs.get(champ);
	if(previous != null) {
	    erreurs.put( champ, previous + message );
	}
	else {
	    erreurs.put( champ, message );
	}
	
    }
    
    private static String getValeurChamp( HttpServletRequest request, String nomChamp ) {
        String valeur = request.getParameter( nomChamp );
        if ( valeur == null || valeur.trim().length() == 0 ) {
            return null;
        } else {
            return valeur;
        }
    }
}
